/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pe.gob.pnp.digitalizar.despachadores;

import java.io.*;
import java.sql.*;
import java.text.*;
import java.util.*;
import java.util.ArrayList;
import pe.gob.pnp.digitalizar.beans.*;
import pe.gob.pnp.factories.*;

/**
 *
 * @author Eduardo
 */
public class ExpedienteDespachador {
    public static List<ExpedienteBean> obtenerDatosExpediente(String codigo) throws SQLException {
        List<ExpedienteBean> expedientes = new ArrayList<ExpedienteBean>();
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "select  P.NPERS_IDENTIFICADOR,";
            sql += " E.NEXPE_IDENTIFICADOR,";
            sql += " E.CEXPE_CODIGOARCHIVO,";
            sql += " TO_CHAR(E.DEXPE_FECHACREACION, 'DD/MM/YYYY') AS DEXPE_FECHACREACION ,";
            sql += " P.CPERS_APEPATERNO||' '||P.CPERS_APEMATERNO||', ' ||P.CPERS_NOMBRE AS CPERS_APENOM,";
            sql += " P.CGRAD_DESCRIPCION";
            sql += " from sisma_expediente E";
            sql += " inner join PERMA_PERSONALPOLICIAL pp";
            sql += " on pp.NPEPO_IDENTIFICADOR = e.NPEPO_IDENTIFICADOR";
            sql += " inner join PERMA_PERSONA p";
            sql += " on p.NPERS_IDENTIFICADOR = pp.NPERS_IDENTIFICADOR";
            sql += " where CEXPE_CODIGOARCHIVO = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, codigo);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ExpedienteBean expediente = new ExpedienteBean();
                expediente.setNpers_identificador(rs.getInt("NPERS_IDENTIFICADOR"));
                expediente.setNexpe_identificador(rs.getInt("NEXPE_IDENTIFICADOR"));
                expediente.setCexpe_codigoarchivo(rs.getString("CEXPE_CODIGOARCHIVO"));
                expediente.setDexpe_fechacreacion(rs.getString("DEXPE_FECHACREACION"));
                expediente.setCpers_apenom(rs.getString("CPERS_APENOM"));
                expediente.setCgrad_descripcion(rs.getString("CGRAD_DESCRIPCION"));
                expediente.setNroFolios(obtenerCntFolios(rs.getInt("NEXPE_IDENTIFICADOR")));
                expedientes.add(expediente);
            }
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return expedientes;
    }

    public static void eliminarExpedienteDigitalizado(String nexpe_identificador) throws SQLException {
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "DELETE FROM DIGMA_FOLIO ";
            String where = " WHERE NDODI_IDENTIFICADOR IN (SELECT NDODI_IDENTIFICADOR ";
            where += " FROM DIGMA_DOCUMENTODIGITALIZADO";
            where += " WHERE NEXPE_IDENTIFICADOR= ?)";
            String sentencia = sql + where;
            PreparedStatement ps = conn.prepareStatement(sentencia);
            ps.setInt(1, Integer.parseInt(nexpe_identificador));
            ps.executeUpdate();
            sql = "DELETE FROM DIGMA_INDEXADO ";
            sentencia = sql + where;
            ps = conn.prepareStatement(sentencia);
            ps.setInt(1, Integer.parseInt(nexpe_identificador));
            ps.executeUpdate();
            sentencia = " DELETE FROM DIGMA_DOCUMENTODIGITALIZADO";
            sentencia += " WHERE NEXPE_IDENTIFICADOR= ?";
            ps = conn.prepareStatement(sentencia);
            ps.setInt(1, Integer.parseInt(nexpe_identificador));
            ps.executeUpdate();
            sentencia = " DELETE FROM SISMA_EXPEDIENTE";
            sentencia += " WHERE NEXPE_IDENTIFICADOR=?";
            ps = conn.prepareStatement(sentencia);
            ps.setInt(1, Integer.parseInt(nexpe_identificador));
            ps.executeUpdate();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
    }


    public static List<ExpedienteBean> obtenerExpedientes(PersonalBean parametros) throws SQLException {
        List<ExpedienteBean> expedientes = new ArrayList<ExpedienteBean>();
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "SELECT E.CEXPE_CODIGOARCHIVO, P.CPERS_APEPATERNO, P.CPERS_APEMATERNO, P.CPERS_NOMBRE, E.DEXPE_FECHACREACION,";
            sql += " DD.NDODI_NROFOLIOS, E.NEXPE_IDENTIFICADOR";
            sql += " FROM SISMA_EXPEDIENTE E";
            sql += " INNER JOIN PERMA_PERSONALPOLICIAL PP";
            sql += " ON PP.NPEPO_IDENTIFICADOR=E.NPEPO_IDENTIFICADOR";
            sql += " INNER JOIN PERMA_PERSONA P";
            sql += " ON P.NPERS_IDENTIFICADOR=PP.NPERS_IDENTIFICADOR";
            sql += " LEFT JOIN DIGMA_DOCUMENTODIGITALIZADO DD";
            sql += " ON DD.NEXPE_IDENTIFICADOR=E.NEXPE_IDENTIFICADOR";
            sql += " AND DD.BDODI_ELIMINADO=0";
            sql += " WHERE P.NPERS_IDENTIFICADOR=?";
            sql += " AND E.BEXPE_ELIMINADO=0";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, parametros.getId());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ExpedienteBean expediente = new ExpedienteBean();
                SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
                String fechaCreacion = df.format(rs.getDate("DEXPE_FECHACREACION"));
                expediente.setFechaCreacion(fechaCreacion);
                expediente.setId(rs.getInt("NEXPE_IDENTIFICADOR"));
                expediente.setNroFolios(obtenerCntFolios(rs.getInt("NEXPE_IDENTIFICADOR")));
                expediente.setNumeroExpediente(rs.getString("CEXPE_CODIGOARCHIVO"));
                PersonalBean personal = new PersonalBean();
                personal.setApellidoMaterno(rs.getString("CPERS_APEMATERNO"));
                personal.setApellidoPaterno(rs.getString("CPERS_APEPATERNO"));
                personal.setNombres(rs.getString("CPERS_NOMBRE"));
                expediente.setPersonal(personal);
                expedientes.add(expediente);
            }
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return expedientes;
    }
    public static int obtenerCntFolios (int expediente)throws SQLException {
        Connection conn = null;
        int nrofolios = 0;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "SELECT NVL(SUM(FOLIOS),0) AS CNT FROM ( ";
            sql += " SELECT DD.NDODI_IDENTIFICADOR, DD.NTIDO_IDENTIFICADOR, ";
            sql += " DD.CDODI_DESCRIPCION, TD.CTIDO_DESCRIPCION, COUNT(*) AS FOLIOS";
            sql += " FROM DIGMA_FOLIO F INNER JOIN  DIGMA_DOCUMENTODIGITALIZADO DD";
            sql += " ON DD.NDODI_IDENTIFICADOR=F.NDODI_IDENTIFICADOR";
            sql += " AND DD.BDODI_ELIMINADO=0 INNER JOIN SISMA_TIPODOCUMENTO TD";
            sql += " ON TD.NTIDO_IDENTIFICADOR=DD.NTIDO_IDENTIFICADOR";
            sql += " AND TD.BTIDO_ELIMINADO=0";
            sql += " WHERE F.BFOLI_ELIMINADO=0  AND ";
            sql += " DD.NEXPE_IDENTIFICADOR=?";
            sql += " GROUP BY DD.NDODI_IDENTIFICADOR, DD.NTIDO_IDENTIFICADOR, DD.CDODI_DESCRIPCION, TD.CTIDO_DESCRIPCION)";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, expediente);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
               nrofolios=rs.getInt("CNT");
            }
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return nrofolios;
    }

    public static void agregarExpediente(ExpedienteBean expediente, PersonalBean persona, int usuario) throws SQLException {
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "INSERT INTO SISMA_EXPEDIENTE(NEXPE_IDENTIFICADOR,BEXPE_DIGITALIZADO,DEXPE_FECHACREACION,NPEPO_IDENTIFICADOR,";
            sql += " CEXPE_CODIGOARCHIVO,NUSUA_IDENTIFICADOR,BEXPE_ELIMINADO)";
            sql += " VALUES(SEQ_NEXPE_IDENTIFICADOR.NEXTVAL,0,SYSDATE,?,?,?,0)";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, persona.getIdPolicial());
            ps.setString(2, expediente.getNumeroExpediente());
            ps.setInt(3, usuario);
            ps.executeUpdate();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
    }

    public static int agregarDocumentoDigitalizado(DocumentoDigitalizadoBean documento, int usuario) throws SQLException {
        Connection conn = null;
        int id = 0;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "INSERT INTO DIGMA_DOCUMENTODIGITALIZADO(NDODI_IDENTIFICADOR,NEXPE_IDENTIFICADOR,NTIDO_IDENTIFICADOR,BDODI_ACTIVO,";
            sql += " NDODI_NROFOLIOS,CDODI_DESCRIPCION,BDODI_ELIMINADO, NUSUA_IDENTIFICADOR)";
            sql += " VALUES(SEQ_NDODI_IDENTIFICADOR.NEXTVAL,?,?,1,?,?,0,?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, documento.getIdExpediente());
            ps.setInt(2, documento.getIdTipoDocumento());
            ps.setInt(3, documento.getNroFolios());
            ps.setString(4, documento.getDescripcion());
            ps.setInt(5, usuario);
            ps.executeUpdate();
            sql = "SELECT SEQ_NDODI_IDENTIFICADOR.CURRVAL AS ID FROM DUAL";
            ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                id = rs.getInt("ID");
            }
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return id;
    }

    public static void actualizarDocumentoDigitalizado(DocumentoDigitalizadoBean documento) throws SQLException {
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "UPDATE DIGMA_DOCUMENTODIGITALIZADO";
            sql += " SET NDODI_NROFOLIOS=?,";
            sql += " CDODI_DESCRIPCION=?";
            sql += " WHERE NDODI_IDENTIFICADOR=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, documento.getNroFolios());
            ps.setString(2, documento.getDescripcion());
            ps.setInt(3, documento.getId());
            ps.executeUpdate();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
    }

    public static void agregarFolios(DocumentoDigitalizadoBean documento, String carpetaActual, List<String> imagenes, int usuario) throws SQLException {
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "INSERT INTO DIGMA_FOLIO(NDODI_IDENTIFICADOR,NFOLI_IDENTIFICADOR,BFOLI_IMAGEN,NFOLI_PAGINA,NESFO_IDENTIFICADOR, CFOLI_NOMBREARCHIVO, NUSUA_IDENTIFICADOR)";
            sql += " VALUES(?,SEQ_NFOLI_IDENTIFICADOR.NEXTVAL,?,?,?,?,?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(4, documento.getEstadoFolio());
            ps.setInt(6, usuario);
            int i = 1;
            for (String imagen : imagenes) {
                String ruta = carpetaActual + "/" + imagen;
                File archivo = new File(ruta);
                FileInputStream in = new FileInputStream(archivo);
                ps.setBinaryStream(2, in, (int) archivo.length());
                ps.setInt(1, documento.getId());
                ps.setInt(3, i);
                ps.setString(5, imagen);

                ps.executeUpdate();
                in.close();
                archivo.delete();
                i++;
            }
        } catch (IOException ex) {
            throw new SQLException(ex);
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
    }

    public static List<ExpedienteDocumentoBean> obtenerExpedientesPorCriterio(String criterio) throws SQLException {
        List<ExpedienteDocumentoBean> expDocs = new ArrayList<ExpedienteDocumentoBean>();
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "SELECT E.CEXPE_CODIGOARCHIVO, P.CPERS_APEPATERNO, ";
            sql += " P.CPERS_APEMATERNO, P.CPERS_NOMBRE, G.CGRAD_DESCRIPCION,";
            sql += " DD.NDODI_NROFOLIOS, E.NEXPE_IDENTIFICADOR, TD.NTIDO_IDENTIFICADOR, ";
            sql += " TD.CTIDO_DESCRIPCION, DD.CDODI_DESCRIPCION,";
            sql += " DD.NDODI_IDENTIFICADOR, E.NPEPO_IDENTIFICADOR";
            sql += " FROM SISMA_EXPEDIENTE E";
            sql += " INNER JOIN DIGMA_DOCUMENTODIGITALIZADO DD";
            sql += " ON E.NEXPE_IDENTIFICADOR=DD.NEXPE_IDENTIFICADOR";
            sql += " AND DD.BDODI_ELIMINADO=0";
            sql += " LEFT JOIN DIGMA_INDEXADO I";
            sql += " ON DD.NDODI_IDENTIFICADOR=I.NDODI_IDENTIFICADOR";
            sql += " AND I.BINDE_ELIMINADO=0";
            sql += " INNER JOIN PERMA_PERSONALPOLICIAL PP";
            sql += " ON E.NPEPO_IDENTIFICADOR=PP.NPEPO_IDENTIFICADOR";
            sql += " INNER JOIN PERMA_PERSONA P";
            sql += " ON P.NPERS_IDENTIFICADOR=PP.NPERS_IDENTIFICADOR";
            sql += " LEFT JOIN SISMA_GRADO G";
            sql += " ON G.NGRAD_IDENTIFICADOR=P.NGRAD_CODIGOSIGE";
            sql += " INNER JOIN SISMA_TIPODOCUMENTO TD";
            sql += " ON TD.NTIDO_IDENTIFICADOR=DD.NTIDO_IDENTIFICADOR";
            sql += " AND TD.BTIDO_ELIMINADO=0";
            sql += " WHERE (UPPER(I.CINDE_VALOR) LIKE UPPER(?) or  dd.CDODI_DESCRIPCION like UPPER(?))";
            sql += " AND E.BEXPE_ELIMINADO=0";
            PreparedStatement ps = conn.prepareStatement(sql);
            if (criterio == null) criterio="";
            ps.setString(1, "%" + criterio + "%");
            ps.setString(2, "%" + criterio + "%");
            System.out.println(criterio);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ExpedienteBean expediente = new ExpedienteBean();
                expediente.setId(rs.getInt("NEXPE_IDENTIFICADOR"));
                expediente.setNumeroExpediente(rs.getString("CEXPE_CODIGOARCHIVO"));
                PersonalBean persona = new PersonalBean();
                persona.setApellidoPaterno(rs.getString("CPERS_APEPATERNO"));
                persona.setApellidoMaterno(rs.getString("CPERS_APEMATERNO"));
                persona.setNombres(rs.getString("CPERS_NOMBRE"));
                persona.setGrado(rs.getString("CGRAD_DESCRIPCION"));
                persona.setIdPolicial(rs.getInt("NPEPO_IDENTIFICADOR"));
                expediente.setPersonal(persona);
                expediente.setNroFolios(obtenerCntFolios(rs.getInt("NEXPE_IDENTIFICADOR")));
                ExpedienteDocumentoBean expDoc = new ExpedienteDocumentoBean();
                expDoc.setExpediente(expediente);
                DocumentoDigitalizadoBean documento = new DocumentoDigitalizadoBean();
                documento.setId(rs.getInt("NDODI_IDENTIFICADOR"));
                documento.setIdTipoDocumento(rs.getInt("NTIDO_IDENTIFICADOR"));
                documento.setDescripcionTipoDocumento(rs.getString("CTIDO_DESCRIPCION"));
                documento.setDescripcion(rs.getString("CDODI_DESCRIPCION"));
                expDoc.setDocumento(documento);
                expDocs.add(expDoc);
            }
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return expDocs;
    }

    public static List<ExpedienteDocumentoBean> obtenerExpedientesPorCodigo(String criterio) throws SQLException {
        List<ExpedienteDocumentoBean> expDocs = new ArrayList<ExpedienteDocumentoBean>();
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "SELECT E.CEXPE_CODIGOARCHIVO, P.CPERS_APEPATERNO, P.CPERS_APEMATERNO, P.CPERS_NOMBRE, G.CGRAD_DESCRIPCION,";
            sql += " DD.NDODI_NROFOLIOS, E.NEXPE_IDENTIFICADOR, TD.NTIDO_IDENTIFICADOR, TD.CTIDO_DESCRIPCION, DD.CDODI_DESCRIPCION,";
            sql += " DD.NDODI_IDENTIFICADOR, E.NPEPO_IDENTIFICADOR ";
            sql += " FROM SISMA_EXPEDIENTE E";
            sql += " LEFT JOIN DIGMA_DOCUMENTODIGITALIZADO DD ON E.NEXPE_IDENTIFICADOR=DD.NEXPE_IDENTIFICADOR AND DD.BDODI_ELIMINADO=0";
            sql += " LEFT JOIN DIGMA_INDEXADO I  ON DD.NDODI_IDENTIFICADOR=I.NDODI_IDENTIFICADOR AND I.BINDE_ELIMINADO=0 ";
            sql += " INNER JOIN PERMA_PERSONALPOLICIAL PP  ON E.NPEPO_IDENTIFICADOR=PP.NPEPO_IDENTIFICADOR";
            sql += " INNER JOIN PERMA_PERSONA P ON P.NPERS_IDENTIFICADOR=PP.NPERS_IDENTIFICADOR";
            sql += " LEFT JOIN SISMA_GRADO G ON G.NGRAD_IDENTIFICADOR=P.NGRAD_CODIGOSIGE";
            sql += " LEFT JOIN SISMA_TIPODOCUMENTO TD ON TD.NTIDO_IDENTIFICADOR=DD.NTIDO_IDENTIFICADOR AND TD.BTIDO_ELIMINADO=0";
            sql += " WHERE E.CEXPE_CODIGOARCHIVO=?";
            sql += " AND E.BEXPE_ELIMINADO=0";
            PreparedStatement ps = conn.prepareStatement(sql);
            if (criterio == null) criterio="";
            ps.setString(1, criterio);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ExpedienteBean expediente = new ExpedienteBean();
                expediente.setId(rs.getInt("NEXPE_IDENTIFICADOR"));
                expediente.setNumeroExpediente(rs.getString("CEXPE_CODIGOARCHIVO"));
                PersonalBean persona = new PersonalBean();
                persona.setApellidoPaterno(rs.getString("CPERS_APEPATERNO"));
                persona.setApellidoMaterno(rs.getString("CPERS_APEMATERNO"));
                persona.setNombres(rs.getString("CPERS_NOMBRE"));
                persona.setGrado(rs.getString("CGRAD_DESCRIPCION"));
                persona.setIdPolicial(rs.getInt("NPEPO_IDENTIFICADOR"));
                expediente.setPersonal(persona);
                expediente.setNroFolios(obtenerCntFolios(rs.getInt("NEXPE_IDENTIFICADOR")));
                ExpedienteDocumentoBean expDoc = new ExpedienteDocumentoBean();
                expDoc.setExpediente(expediente);
                DocumentoDigitalizadoBean documento = new DocumentoDigitalizadoBean();
                documento.setId(rs.getInt("NDODI_IDENTIFICADOR"));
                documento.setIdTipoDocumento(rs.getInt("NTIDO_IDENTIFICADOR"));
                documento.setDescripcionTipoDocumento(rs.getString("CTIDO_DESCRIPCION"));
                documento.setDescripcion(rs.getString("CDODI_DESCRIPCION"));
                expDoc.setDocumento(documento);
                expDocs.add(expDoc);
            }
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return expDocs;
    }

    public static List<ExpedienteDocumentoBean> obtenerExpedientesPorPersona(String criterio) throws SQLException {
        List<ExpedienteDocumentoBean> expDocs = new ArrayList<ExpedienteDocumentoBean>();
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "SELECT E.CEXPE_CODIGOARCHIVO, P.CPERS_APEPATERNO, ";
            sql += " P.CPERS_APEMATERNO, P.CPERS_NOMBRE, G.CGRAD_DESCRIPCION,";
            sql += " DD.NDODI_NROFOLIOS, E.NEXPE_IDENTIFICADOR, TD.NTIDO_IDENTIFICADOR, ";
            sql += " TD.CTIDO_DESCRIPCION, DD.CDODI_DESCRIPCION,DD.NDODI_IDENTIFICADOR, E.NPEPO_IDENTIFICADOR";

            sql += " FROM SISMA_EXPEDIENTE E INNER JOIN DIGMA_DOCUMENTODIGITALIZADO DD ON E.NEXPE_IDENTIFICADOR=DD.NEXPE_IDENTIFICADOR";
            sql += " LEFT JOIN DIGMA_INDEXADO I ON DD.NDODI_IDENTIFICADOR=I.NDODI_IDENTIFICADOR AND I.BINDE_ELIMINADO=0";
            sql += " INNER JOIN PERMA_PERSONALPOLICIAL PP ON E.NPEPO_IDENTIFICADOR=PP.NPEPO_IDENTIFICADOR";
            sql += " INNER JOIN PERMA_PERSONA P ON P.NPERS_IDENTIFICADOR=PP.NPERS_IDENTIFICADOR";
            sql += " LEFT JOIN SISMA_GRADO G ON G.NGRAD_IDENTIFICADOR=P.NGRAD_CODIGOSIGE";
            sql += " LEFT JOIN SISMA_TIPODOCUMENTO TD ON TD.NTIDO_IDENTIFICADOR=DD.NTIDO_IDENTIFICADOR AND TD.BTIDO_ELIMINADO=0";
            sql += " WHERE UPPER(P.CPERS_APEPATERNO || ' ' || P.CPERS_APEMATERNO || ', ' || P.CPERS_NOMBRE) LIKE UPPER(?)";
            sql += " AND E.BEXPE_ELIMINADO=0";
            PreparedStatement ps = conn.prepareStatement(sql);
            if (criterio == null) criterio="";
            ps.setString(1, "%" + criterio + "%");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ExpedienteBean expediente = new ExpedienteBean();
                expediente.setId(rs.getInt("NEXPE_IDENTIFICADOR"));
                expediente.setNumeroExpediente(rs.getString("CEXPE_CODIGOARCHIVO"));
                PersonalBean persona = new PersonalBean();
                persona.setApellidoPaterno(rs.getString("CPERS_APEPATERNO"));
                persona.setApellidoMaterno(rs.getString("CPERS_APEMATERNO"));
                persona.setNombres(rs.getString("CPERS_NOMBRE"));
                persona.setGrado(rs.getString("CGRAD_DESCRIPCION"));
                persona.setIdPolicial(rs.getInt("NPEPO_IDENTIFICADOR"));
                expediente.setPersonal(persona);
                expediente.setNroFolios(obtenerCntFolios(rs.getInt("NEXPE_IDENTIFICADOR")));
                ExpedienteDocumentoBean expDoc = new ExpedienteDocumentoBean();
                expDoc.setExpediente(expediente);
                DocumentoDigitalizadoBean documento = new DocumentoDigitalizadoBean();
                documento.setId(rs.getInt("NDODI_IDENTIFICADOR"));
                documento.setIdTipoDocumento(rs.getInt("NTIDO_IDENTIFICADOR"));
                documento.setDescripcionTipoDocumento(rs.getString("CTIDO_DESCRIPCION"));
                documento.setDescripcion(rs.getString("CDODI_DESCRIPCION"));
                expDoc.setDocumento(documento);
                expDocs.add(expDoc);
            }
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return expDocs;
    }

        public static List<ExpedienteDocumentoBean> obtenerExpedientesParametros(PersonalBean parametros) throws SQLException {
        List<ExpedienteDocumentoBean> expedientes = new ArrayList<ExpedienteDocumentoBean>();
        Connection conn = null;
        StringBuilder sql = new StringBuilder();
        try {
            conn = ConnectionPool.obtenerConexion();
            sql.append(" SELECT  P.CPERS_APEPATERNO, P.CPERS_APEMATERNO,P.CPERS_NOMBRE, ");
            sql.append(" TD.NTIDO_IDENTIFICADOR,TD.CTIDO_DESCRIPCION,G.CGRAD_DESCRIPCION,");
            sql.append(" DD.NDODI_NROFOLIOS,DD.CDODI_DESCRIPCION,DD.NDODI_IDENTIFICADOR, ");
            sql.append(" E.CEXPE_CODIGOARCHIVO,E.NEXPE_IDENTIFICADOR,  E.DEXPE_FECHACREACION,E.NPEPO_IDENTIFICADOR");
            sql.append(" FROM SISMA_EXPEDIENTE E ");
            sql.append(" INNER JOIN PERMA_PERSONALPOLICIAL PP ON PP.NPEPO_IDENTIFICADOR=E.NPEPO_IDENTIFICADOR");
            sql.append(" INNER JOIN PERMA_PERSONA P ON P.NPERS_IDENTIFICADOR=PP.NPERS_IDENTIFICADOR");
            sql.append(" LEFT JOIN DIGMA_DOCUMENTODIGITALIZADO DD ON DD.NEXPE_IDENTIFICADOR=E.NEXPE_IDENTIFICADOR");
            sql.append(" LEFT JOIN SISMA_GRADO G ON G.NGRAD_IDENTIFICADOR=P.NGRAD_CODIGOSIGE AND DD.BDODI_ELIMINADO=0");
            sql.append(" LEFT JOIN SISMA_TIPODOCUMENTO TD ON TD.NTIDO_IDENTIFICADOR=DD.NTIDO_IDENTIFICADOR AND TD.BTIDO_ELIMINADO=0");
            sql.append(" WHERE P.NPERS_IDENTIFICADOR=?");
            sql.append(" AND E.BEXPE_ELIMINADO=0");
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ps.setInt(1, parametros.getId());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
               ExpedienteBean expediente = new ExpedienteBean();
                expediente.setId(rs.getInt("NEXPE_IDENTIFICADOR"));
                expediente.setNumeroExpediente(rs.getString("CEXPE_CODIGOARCHIVO"));
                PersonalBean persona = new PersonalBean();
                persona.setApellidoPaterno(rs.getString("CPERS_APEPATERNO"));
                persona.setApellidoMaterno(rs.getString("CPERS_APEMATERNO"));
                persona.setNombres(rs.getString("CPERS_NOMBRE"));
                persona.setGrado(rs.getString("CGRAD_DESCRIPCION"));
                persona.setIdPolicial(rs.getInt("NPEPO_IDENTIFICADOR"));
                expediente.setPersonal(persona);
                expediente.setNroFolios(obtenerCntFolios(rs.getInt("NEXPE_IDENTIFICADOR")));
                ExpedienteDocumentoBean expDoc = new ExpedienteDocumentoBean();
                expDoc.setExpediente(expediente);
                DocumentoDigitalizadoBean documento = new DocumentoDigitalizadoBean();
                documento.setId(rs.getInt("NDODI_IDENTIFICADOR"));
                documento.setIdTipoDocumento(rs.getInt("NTIDO_IDENTIFICADOR"));
                documento.setDescripcionTipoDocumento(rs.getString("CTIDO_DESCRIPCION"));
                documento.setDescripcion(rs.getString("CDODI_DESCRIPCION"));
                expDoc.setDocumento(documento);
                expedientes.add(expDoc);
            }
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return expedientes;
    }
}
